Using jOOQ With Spring: Sorting and Pagination

您所在的位置：网站首页 › spring boot pagination and sorting example › Using jOOQ With Spring: Sorting and Pagination

Using jOOQ With Spring: Sorting and Pagination

#Using jOOQ With Spring: Sorting and Pagination| 来源: 网络整理| 查看: 265

JOOQ is a library which helps us to get in control of our SQL. It can generate code from our database and help us to build typesafe database queries by using its fluent API.

The earlier parts of this tutorial have taught us how we can configure the application context of our application, generate code from our database, and add CRUD operations to our jOOQ repository.

This time we will learn how we can implement a simple search function which supports sorting and pagination.

Let鈥檚 get started.

Additional Reading: Using jOOQ With Spring: Configuration is the first part of this tutorial, and it describes how you can configure the application context of a Spring application which uses jOOQ. You can understand this blog post without reading the first part of this tutorial, but if you want to really use jOOQ in a Spring powered application, I recommend that you read the first part of this tutorial as well. Using jOOQ With Spring: Code Generation is the second part of this tutorial, and it describes how we can reverse-engineer our database and create the jOOQ query classes which represents different database tables, records, and so on. Because these classes are the building blocks of typesafe SQL queries, I recommend that you read the second part of this tutorial before reading this blog post. Using jOOQ With Spring: CRUD describes how we can add CRUD operations for a simple application which manages todo entries. Because it covers the information needed to create jOOQ repositories with Spring, I recommend that you read it before reading this blog post. Adding Pagination and Sorting Support to the Web Layer

When we implement a search function which must support both pagination and sorting, we have to figure out a way to provide the page number, page size, name of the sort field, and the sort order to our backend.

We could of course implement a component which supports this but it isn鈥檛 so simple than it sounds. It is pretty easy to create a HandlerMethodArgumentResolver which extracts this information from a HTTP request and converts it to a object which is then passed forward to our controller method as a method argument. The problem is that there are many "exceptional" situations which makes this task pretty tricky. For example,

If this information is not found from the HTTP request, we must fallback to default values. If the required information is missing (e.g. page number is given without specifying page size), we must either fallback to default values or return an error to the user of our REST API.

Luckily, we don鈥檛 have to implement this component. The Spring Data Commons project has a component which extracts paging and sorting information from HTTP requests and allows us to inject this information into controller methods.

Let鈥檚 find out we can get the Spring Data Commons binaries with Maven.

Getting the Required Dependencies with Maven

We can get the required binaries with Maven by adding the following dependency declaration to the dependencies section of our POM file:

org.springframework.data spring-data-commons 1.7.1.RELEASE

Our next step is to make some changes to the application context configuration of our example application. Let鈥檚 move on and find out what kind of changes we must do.

Configuring the Application Context

We can enable the web pagination support of Spring Data by making a one simple change to the application context configuration class that configures the web layer of our example application. We have to annotate the configuration class with the @EnableSpringDataWebSupport annotation. This ensures that the required beans are registered automatically.

The relevant part of the WebAppContext class looks as follows:

import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.data.web.config.EnableSpringDataWebSupport; import org.springframework.web.servlet.config.annotation.EnableWebMvc; import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter; @Configuration @ComponentScan({ "net.petrikainulainen.spring.jooq.common.controller", "net.petrikainulainen.spring.jooq.todo.controller" }) @EnableWebMvc @EnableSpringDataWebSupport public class WebAppContext extends WebMvcConfigurerAdapter { //Other methods are omitted for the sake of clarity } Additional Reading: The API documation of the @EnableSpringDataWebSupport annotation provides more information about the beans which are registered when this annotation is used.

That is it. We have now made the required changes to the application context configuration of our example application. Let鈥檚 find out how we can use the web pagination support in our application.

Using Web Pagination

When we want to sort and paginate our query results, we have to follow these steps:

Add the paging and sorting configuration to the HTTP request. Add a Pageable method parameter to the controller method.

First, we can add the paging and sorting configuration to the HTTP request by using the following request parameters:

The page request parameter specifies the requested page number. The size request parameter specifies the size of the requested page. The sort request parameter specifies the properties which are used to sort the query results. This value of this request parameter must follow this syntax: property,property(,ASC|DESC). If the sort direction isn鈥檛 given, the results are sorted in ascending order. If you want to switch the sort order, you have to use multiple sort parameters (e.g. ?sort=title&sort=id,desc).

Second, we have to add a Pageable method parameter to our controller method. The relevant part of the TodoController class looks as follows:

import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Pageable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import javax.validation.Valid; import java.util.List; @RestController @RequestMapping("/api/todo") public class TodoController { private final TodoCrudService crudService; private final TodoSearchService searchService; @Autowired public TodoController(TodoCrudService crudService, TodoSearchService searchService) { this.crudService = crudService; this.searchService = searchService; } @RequestMapping(value = "/search", method = RequestMethod.GET) public Page findBySearchTerm(@RequestParam("searchTerm") String searchTerm, Pageable pageable) { return searchService.findBySearchTerm(searchTerm, pageable); } } Additional Reading: Section 1.4.1: Web Support of the Spring Data Commons Reference Manual provides more information about the web support of Spring Data. The Javadoc of the Pageable interface

We can now add the search function to our jOOQ repository. Let鈥檚 find out how this is done.

Implementing the Repository Layer

The first thing that we have to do is to add a new public method to the TodoRepository interface. The findBySearchTerm(String searchTerm, Pageable pageable) method finds the todo entries whose title or description contains the given search term and returns the query results by following the paging and sorting configuration given as a method parameter.

The relevant part of the TodoRepository interface looks as follows:

import org.springframework.data.domain.Pageable; import java.util.List; public interface TodoRepository { public Page findBySearchTerm(String searchTerm, Pageable pageable); //Other methods are omitted for the sake of clarity }

The implementation of this method has three responsibilities:

It must find the todo entries whose title or description contains the given search term. It must process the sorting and paging options found from the Pageable object and transform them into a form which is understood by jOOQ. It must create the returned Page object. This object contains information about the returned page and the actual search results.

Let鈥檚 move on and find out how we can find todo entries whose title or description contains the given search term.

Implementing the Search Query

We can implement the search query by following these steps:

Add the findBySearchTerm(String searchTerm, Pageable pageable) method to the JOOQTodoRepository class. Annotate the method with the @Transactional annotation and set the value of its readOnly attribute to true. This ensures that the SELECT statement is executed inside a read-only transaction. Implement the findBySearchTerm() method by following these steps: Create the like expression that is used in our database query. Create a new SELECT statement by calling the selectFrom(Table table) method of the DSLContext interface and specify that you want to select information from the todos table. Specify the WHERE clause of the SELECT statement by calling the where(Condition... conditions) method of the SelectWhereStep interface. Create the method parameter of this method by following these steps: Create the like conditions for the description and title columns of the todos table by calling the likeIgnoreCase(String value) method of the Field interface. Pass the created like expression as a method parameter. Combine the created like conditions by using the or(Condition other) method of the Condition interface. Get a list of TodosRecord objects by calling the fetchInto(Class type) method of the ResultQuery interface. Pass a TodosRecord.class object as a method parameter. Transform the list of TodosRecord objects into a list of Todo objects by calling the private convertQueryResultsToModelObjects() method. This method iterates the list of TodosRecord objects and converts each TodosRecord object into a Todo object by calling the convertQueryResultToModelObject() method. Each Todo object is added to a list which is returned when all TodosRecord objects have been processed. Create a new PageImpl object and pass the list of Todo objects as a constructor argument. Return the created PageImpl object.

The source code of our implementation looks as follows:

import org.jooq.DSLContext; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Pageable; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import java.util.ArrayList; import java.util.List; import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS; @Repository public class JOOQTodoRepository implements TodoRepository { private final DateTimeService dateTimeService; private final DSLContext jooq; //The constructor is omitted for the sake of clarity @Transactional(readOnly = true) @Override public Page findBySearchTerm(String searchTerm, Pageable pageable) { String likeExpression = "%" + searchTerm + "%"; List queryResults = jooq.selectFrom(TODOS) .where( TODOS.DESCRIPTION.likeIgnoreCase(likeExpression) .or(TODOS.TITLE.likeIgnoreCase(likeExpression)) ) .fetchInto(TodosRecord.class); List todoEntries = convertQueryResultsToModelObjects(queryResults); return new PageImpl(todoEntries); } private List convertQueryResultsToModelObjects(List queryResults) { List todoEntries = new ArrayList(); for (TodosRecord queryResult : queryResults) { Todo todoEntry = convertQueryResultToModelObject(queryResult); todoEntries.add(todoEntry); } return todoEntries; } private Todo convertQueryResultToModelObject(TodosRecord queryResult) { return Todo.getBuilder(queryResult.getTitle()) .creationTime(queryResult.getCreationTime()) .description(queryResult.getDescription()) .id(queryResult.getId()) .modificationTime(queryResult.getModificationTime()) .build(); } //Other methods are omitted for the sake of clarity } Additional Reading: The database query of this example is very simple. If you need to create more complex database queries, you should read the section 4.7 Conditional expressions of the jOOQ reference manual. It describes how you can use conditional expressions in your database queries. The Javadoc of the DSLContext interface The Javadoc of the SelectWhereStep interface The Javadoc of the Field interface The Javadoc of the Condition interface The Javadoc of the ResultQuery interface The Javadoc of the PageImpl class

We have now created a repository method which searches todo entries from the database. Our next step is to sort the query results of this database query.

Sorting the Query Results

Before we can sort the query results of our search query, we must understand how we can get the sorting options of our database query from the Pageable object.

We can get a reference to a Sort object by calling the getSort() method of the Pageable interface. This object contains the sorting options found from the HTTP request. The Sort object can contain zero or more sorting options. The iterator() method of the Sort class returns an Iterator object which we can use when we want to process each sorting option of our database query. The Sort.Order class contains the property name and the sort direction.

In other words, we have to fulfil the following requirements:

We must support a situation where no sort options are specified. We must support a situation where our query results are sorted by using multiple columns. We must assume that each column has its own sort order.

We can fulfil these requirements by making the following changes to the JOOQTodoRepository class:

Add a private getTableField(String sortFieldName) method to the repository class and implement this method by following these steps: Use reflection to get the Field object which provides information about the requested field of the TODOS class. If the field isn't found or we cannot access it, throw a new InvalidDataAccessApiUsageException. If the field is found, cast the returned Field object into a TableField object and return it. Add a private convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) method to the repository class and implement the method by following these steps: If the sort order of this field is ascending, call the asc() method of the Field interface and return the returned object. Otherwise call the desc() method of the Field interface and return the returned object. Add a private getSortFields(Sort sortSpecification) method to the repository class and implement it by following these steps: Create a new Collection which contains SortField objects. If the sort options are not found, return an empty Collection object. Iterate the Sort.Order objects found from the Sort object given as a method parameter, and process each Sort.Order object by following these steps: Convert each Sort.Order object into a SortField object by using the getTableField() and convertTableFieldToSortField() methods. Add each SortField object to the Collection which was created at step one. Return the Collection of SortField objects. Sort the query results by following these steps: Get the Sort object by calling the getSort() method of the Pageable interface. Get the Collection> object as a method parameter.

The source code of our implementation looks as follows (the relevant part are highlighted):

import org.jooq.DSLContext; import org.jooq.SortField; import org.jooq.TableField; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.InvalidDataAccessApiUsageException; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.util.List; import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS; @Repository public class JOOQTodoRepository implements TodoRepository { private final DateTimeService dateTimeService; private final DSLContext jooq; //The constructor is omitted for the sake of clarity @Transactional(readOnly = true) @Override public Page findBySearchTerm(String searchTerm, Pageable pageable) { String likeExpression = "%" + searchTerm + "%"; List queryResults = jooq.selectFrom(TODOS) .where( TODOS.DESCRIPTION.likeIgnoreCase(likeExpression) .or(TODOS.TITLE.likeIgnoreCase(likeExpression)) ) .orderBy(getSortFields(pageable.getSort())) .fetchInto(TodosRecord.class); List todoEntries = convertQueryResultsToModelObjects(queryResults); return new PageImpl(todoEntries); } private Collection> querySortFields = new ArrayList(); if (sortSpecification == null) { return querySortFields; } Iterator specifiedFields = sortSpecification.iterator(); while (specifiedFields.hasNext()) { Sort.Order specifiedField = specifiedFields.next(); String sortFieldName = specifiedField.getProperty(); Sort.Direction sortDirection = specifiedField.getDirection(); TableField tableField = getTableField(sortFieldName); SortField querySortField = convertTableFieldToSortField(tableField, sortDirection); querySortFields.add(querySortField); } return querySortFields; } private TableField getTableField(String sortFieldName) { TableField sortField = null; try { Field tableField = TODOS.getClass().getField(sortFieldName); sortField = (TableField) tableField.get(TODOS); } catch (NoSuchFieldException | IllegalAccessException ex) { String errorMessage = String.format("Could not find table field: {}", sortFieldName); throw new InvalidDataAccessApiUsageException(errorMessage, ex); } return sortField; } private SortField convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) { if (sortDirection == Sort.Direction.ASC) { return tableField.asc(); } else { return tableField.desc(); } } private List convertQueryResultsToModelObjects(List queryResults) { List todoEntries = new ArrayList(); for (TodosRecord queryResult : queryResults) { Todo todoEntry = convertQueryResultToModelObject(queryResult); todoEntries.add(todoEntry); } return todoEntries; } private Todo convertQueryResultToModelObject(TodosRecord queryResult) { return Todo.getBuilder(queryResult.getTitle()) .creationTime(queryResult.getCreationTime()) .description(queryResult.getDescription()) .id(queryResult.getId()) .modificationTime(queryResult.getModificationTime()) .build(); } //The other methods are omitted for the sake of clarity } This solution works but it leaks the implementation details of our repository layer (and database) to the clients of our REST API. We could avoid this by specifying a group of allowed aliases for the column names and implement a translation component which converts these aliases into the field names of the TODOS class.

However, because it would increase the complexity of our repository class, we will not do it.

This is actually a great example of a leaky abstraction. This term was originally popularized by Joel Spolsky. He "invented" the law of leaky abstractions which states that:

All non-trivial abstractions, to some degree, are leaky.

Additional Reading:

You can get more information about the ORDER BY clause by reading the section 4.3.3.9 The ORDER BY clause of the jOOQ reference manual. The Javadoc of the Sort class The Javadoc of the Field interface The Javadoc of the TableField interface The Javadoc of the SortField interface The Javadoc of the SelectSeekStepN interface

We have now added sorting support to our search query. Let鈥檚 move on and finish our search function by adding pagination support to the findBySearchTerm() method.

Paginating the Query Results

We can paginate the query results of our search query by adding the LIMIT .. OFFSET clause to our database query. We can do this by making the following changes to the implementation of our database query:

Specify the number of returned rows by calling the limit(int NumberOfRows) method of the SelectLimitStep interface and pass the page size a method parameter (You can get the page size by calling the getPageSize() method of the Pageable interface). Specify the offset by calling the offset(int offset) method of the SelectOffsetStep interface and pass the offset as a method parameter (You can get the offset by calling the getOffset() method of the Pageable interface). Add a private long findCountByLikeExpression(String likeExpression) method to the repository class. Implement this method by following these steps: Find the number of todo entries by calling the fetchCount(Select query) method of the DSLContext class and pass the used SELECT query as method parameter. Return the number of todo entries. Get the number of todo entries, which matches with the given like expression, by calling the private findCountByLikeExpression() method in the findBySearchTerm() method. Pass the used like expression as a method paramer. Create a new PageImpl object and pass the following information as constructor arguments: The list which contains the found todo entries. The Pageable object given as a method parameter. The number of todo entries which matches with the given like expression. Return the created PageImpl object.

After we have made these changes to our repository method, the source code of our repository method looks as follows (the changes are highlighted):

import org.jooq.DSLContext; import org.jooq.SortField; import org.jooq.TableField; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.InvalidDataAccessApiUsageException; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.util.List; import static net.petrikainulainen.spring.jooq.todo.db.tables.Todos.TODOS; @Repository public class JOOQTodoRepository implements TodoRepository { private final DateTimeService dateTimeService; private final DSLContext jooq; //The constructor is omitted for the sake of clarity @Transactional(readOnly = true) @Override public Page findBySearchTerm(String searchTerm, Pageable pageable) { String likeExpression = "%" + searchTerm + "%"; List queryResults = jooq.selectFrom(TODOS) .where( TODOS.DESCRIPTION.likeIgnoreCase(likeExpression) .or(TODOS.TITLE.likeIgnoreCase(likeExpression)) ) .orderBy(getSortFields(pageable.getSort())) .limit(pageable.getPageSize()).offset(pageable.getOffset()) .fetchInto(TodosRecord.class); List todoEntries = convertQueryResultsToModelObjects(queryResults); long totalCount = findCountByLikeExpression(likeExpression); return new PageImpl(todoEntries, pageable, totalCount); } private long findCountByLikeExpression(String likeExpression) { return jooq.fetchCount(jooq.select() .from(TODOS) .where( TODOS.DESCRIPTION.likeIgnoreCase(likeExpression) .or(TODOS.TITLE.likeIgnoreCase(likeExpression)) ) ); } private Collection> querySortFields = new ArrayList(); if (sortSpecification == null) { return querySortFields; } Iterator specifiedFields = sortSpecification.iterator(); while (specifiedFields.hasNext()) { Sort.Order specifiedField = specifiedFields.next(); String sortFieldName = specifiedField.getProperty(); Sort.Direction sortDirection = specifiedField.getDirection(); TableField tableField = getTableField(sortFieldName); SortField querySortField = convertTableFieldToSortField(tableField, sortDirection); querySortFields.add(querySortField); } return querySortFields; } private TableField getTableField(String sortFieldName) { TableField sortField = null; try { Field tableField = TODOS.getClass().getField(sortFieldName); sortField = (TableField) tableField.get(TODOS); } catch (NoSuchFieldException | IllegalAccessException ex) { String errorMessage = String.format("Could not find table field: {}", sortFieldName); throw new InvalidDataAccessApiUsageException(errorMessage, ex); } return sortField; } private SortField convertTableFieldToSortField(TableField tableField, Sort.Direction sortDirection) { if (sortDirection == Sort.Direction.ASC) { return tableField.asc(); } else { return tableField.desc(); } } private List convertQueryResultsToModelObjects(List queryResults) { List todoEntries = new ArrayList(); for (TodosRecord queryResult : queryResults) { Todo todoEntry = convertQueryResultToModelObject(queryResult); todoEntries.add(todoEntry); } return todoEntries; } private Todo convertQueryResultToModelObject(TodosRecord queryResult) { return Todo.getBuilder(queryResult.getTitle()) .creationTime(queryResult.getCreationTime()) .description(queryResult.getDescription()) .id(queryResult.getId()) .modificationTime(queryResult.getModificationTime()) .build(); } //Other methods are omitted for the sake of clarity } Additional Reading: You can the more information about the LIMIT .. OFFSET clause by reading the section 4.3.3.10 The LIMIT .. OFFSET clause of the jOOQ reference manual. The Javadoc of the DSLContext interface The Javadoc of the SelectLimitStep interface The Javadoc of the SElectOffsetStep interface

If you need to implement "an eternal scroll" (like the one Facebook has in the timeline), you should consider using the seek method. You can get more information about this from the jOOQ website:

Faster SQL Paging with jOOQ Using the Seek Method Faster SQL Pagination with Keysets, Continued The SEEK clause @ jOOQ Reference Manual

That is all folks. Let鈥檚 move on and summarize what we learned from this blog post.

Summary

We have now implemented a search function that supports sorting and pagination. This tutorial has taught us four things:

We learned how we can use the web pagination support of the Spring Data Commons project. We learned how we can add the ORDER BY clause to a database query. We learned how we can add the LIMIT .. OFFSET clause to a database query. We learned how we can create new Page objects that contain our query results and useful pagination metadata.

The next part of this tutorial describes how we can integrate Spring Data JPA and jOOQ, and more importantly, why we should do it.

P.S. You can get the example application of this blog post from Github.

If you want to use jOOQ with Spring Framework, you should read my Using jOOQ With Spring tutorial.

【本文地址】

Using jOOQ With Spring: Sorting and Pagination

Using jOOQ With Spring: Sorting and Pagination

今日新闻

推荐新闻